package Design;

import javax.swing.*;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;

public class TrainDao {
    Connection con;
    Statement st;
    ResultSet rs;

    public TrainDao() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String conStr = "jdbc:mysql://localhost:3306/passenger";
            String userName = "root";
            String password = "root";
            //定义数据库连接字符串对象,访问数据库的用户名和密码
            con = DriverManager.getConnection(conStr, userName, password);
            //用驱动管理类的方法来创建数据库连接对象
            System.out.println("已经成功创建了数据库和应用程序之间的连接");
            st = con.createStatement();

        } catch (ClassNotFoundException e) {
            //加载驱动时需要捕捉该类异常
            e.printStackTrace();
        } catch (SQLException e) {
            //创建数据库连接对象时需要捕捉该异常
            e.printStackTrace();
        }
    }

    // 增加
    public Boolean insertTrainData(TrainInfo trainInfo) {
        String sql = "INSERT INTO passenger (id, origin, terminus, time1, time2, price, type, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement statement = con.prepareStatement(sql)) {
            statement.setString(1, trainInfo.getId());
            statement.setString(2, trainInfo.getOrigin());
            statement.setString(3, trainInfo.getTerminus());
            statement.setString(4, trainInfo.getTime1());
            statement.setString(5, trainInfo.getTime2());
            statement.setString(6, trainInfo.getPrice().toString());
            statement.setString(7, trainInfo.getType());
            statement.setString(8, trainInfo.getDate());
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除
    public void deleteTrainDataById(String id) {
        String sql = "DELETE FROM passenger WHERE id = ?";
        try (PreparedStatement statement = con.prepareStatement(sql)) {
            statement.setString(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询
    public  void searchTrainData(String Origin, String Terminus) {
        // 构建查询条件
        String query = "SELECT * FROM trains WHERE origin = ? AND terminus = ?";

        // 使用PreparedStatement来执行查询
        try (PreparedStatement statement = con.prepareStatement(query)) {
            statement.setString(1, Origin);
            statement.setString(2, Terminus);

            // 执行查询
            ResultSet resultSet = statement.executeQuery();

            // 处理结果集
            while (resultSet.next()) {
                // 获取列车的所有信息
                String date = resultSet.getString("date");
                String id = resultSet.getString("id");
                String origin = resultSet.getString("origin");
                String terminus = resultSet.getString("terminus");
                String time1 = resultSet.getString("time1");
                String time2 = resultSet.getString("time2");
                String price = resultSet.getString("price");
                String type = resultSet.getString("type");

                // 这里可以添加代码来处理查询到的每行数据
                // 例如，更新界面上的表格或者列表
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 这里可以添加代码来处理异常，比如显示错误消息给用户
        }
    }

    // 查询
    public  TrainInfo searchTrainDataById(String id) {
        // 构建查询条件
        String query = "SELECT * FROM passenger WHERE id = ?";

        // 使用PreparedStatement来执行查询
        try (PreparedStatement statement = con.prepareStatement(query)) {
            statement.setString(1, id);
            // 执行查询
            ResultSet resultSet = statement.executeQuery();

            // 处理结果集
            while (resultSet.next()) {
                // 获取列车的所有信息
                String date = resultSet.getString("date");
                id = resultSet.getString("id");
                String origin = resultSet.getString("origin");
                String terminus = resultSet.getString("terminus");
                String time1 = resultSet.getString("time1");
                String time2 = resultSet.getString("time2");
                Double price = resultSet.getDouble("price");
                String type = resultSet.getString("type");

                // 这里可以添加代码来处理查询到的每行数据
                // 例如，更新界面上的表格或者列表
                TrainInfo trainInfo = new TrainInfo(date,id,origin,terminus,time1,time2,price,type);
                return trainInfo;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            // 这里可以添加代码来处理异常，比如显示错误消息给用户
        }
        return null;
    }



    // 修改
    public void updateTrainData(String id,TrainInfo trainInfo) {
            // 更新数据库中的数据
            String sql = "UPDATE passenger SET id = ?, origin = ?, terminus = ?, time1 = ?, time2 = ?, price = ?, type = ?, date = ? where id = ?";
            try (PreparedStatement statement = con.prepareStatement(sql)) {
                statement.setString(1, trainInfo.getId());
                statement.setString(2, trainInfo.getOrigin());
                statement.setString(3, trainInfo.getTerminus());
                statement.setString(4, trainInfo.getTime1());
                statement.setString(5, trainInfo.getTime2());
                statement.setString(6, trainInfo.getPrice().toString());
                statement.setString(7, trainInfo.getType());
                statement.setString(8, trainInfo.getDate());
                statement.setString(9, id);
                System.out.println("-------");
                System.out.println(statement.executeUpdate());
            } catch (SQLException e) {
                e.printStackTrace();
                // 这里可以添加代码来处理异常，比如显示错误消息给用户
            }
    }

    public static void main(String[] args) {
        new TrainDao();
    }

    //查询所有
    public ArrayList<TrainInfo> searchTrainDataAll() {
        String query = "SELECT * FROM passenger";
        ArrayList<TrainInfo> trainInfos = new ArrayList<>();
        // 使用PreparedStatement来执行查询
        try (PreparedStatement statement = con.prepareStatement(query)) {
            // 执行查询
            ResultSet resultSet = statement.executeQuery();
            // 处理结果集
            while (resultSet.next()) {
                // 获取列车的所有信息
                String date = resultSet.getString("date");
                String id = resultSet.getString("id");
                String origin = resultSet.getString("origin");
                String terminus = resultSet.getString("terminus");
                String time1 = resultSet.getString("time1");
                String time2 = resultSet.getString("time2");
                Double price = resultSet.getDouble("price");
                String type = resultSet.getString("type");
                TrainInfo trainInfo= new TrainInfo(date,id,origin,terminus,time1,time2,price,type);
                trainInfos.add(trainInfo);
            }
            return trainInfos;

        } catch (SQLException e) {
            e.printStackTrace();
            // 这里可以添加代码来处理异常，比如显示错误消息给用户
        }
        return null;
    }
}
